/**************************************************/

const startTime = "2024-12-01 00:00:00";
const endTime = "2025-02-28 23:59:59";
const hourDispose = 40;

/**************************************************/

const fs = require("fs");
const path = require("path");
const mysql = require("mysql");
const dayjs = require("dayjs");

const execSql = async (query) => {
  const host = "localhost";
  const user = "cdapp";
  const password = "gzzxjjwt1";
  const database = "cdapp";
  // 创建数据库连接
  const connection = mysql.createConnection({ host, user, password, database });
  return new Promise((resolve, reject) => {
    // 连接到数据库
    connection.connect();

    // 执行查询
    connection.query(query, (error, results, fields) => {
      if (error) {
        // 如果查询出错，关闭连接并拒绝 Promise
        connection.end();
        return reject(error);
      }

      // 遍历查询结果
      const parsedResults = results.map((row) => {
        // 遍历每一行的字段
        for (const key in row) {
          if (typeof row[key] === "string") {
            try {
              // 尝试将字符串解析为 JSON
              const parsedValue = JSON.parse(row[key]);
              // 如果解析成功，则将字段值替换为解析后的对象
              row[key] = parsedValue;
            } catch (e) {
              // 如果解析失败，说明不是 JSON 字符串，忽略
            }
          }
        }
        return row; // 返回处理后的行
      });

      // 关闭数据库连接
      connection.end();

      // 返回处理后的结果
      resolve(parsedResults);
    });
  });
};

const getParam = async (item) => {
  const sql = `SELECT \`content\` FROM \`2kt_params\` WHERE \`item\` = '${item}'`;
  const results = await execSql(sql);
  if (results && results.length === 1) {
    // 如果有且仅有一个查询结果
    const content = results[0].content;

    if (!isNaN(content)) {
      // 值为数字
      if (Number.isInteger(parseFloat(content))) {
        // 数值为整数
        return parseInt(content, 10);
      } else {
        // 数值为小数
        return parseFloat(content);
      }
    } else {
      // 结果不为数字
      try {
        const contentJson = JSON.parse(content);
        // 结果为JSON
        return contentJson;
      } catch (error) {
        // 返回原始结果
        return content;
      }
    }
  } else {
    // 如果没有查询结果或者查询到多个结果
    return null;
  }
};

const main = async () => {
  const flowBrief = await getFlowBrief();
  const sectorBrief = await getSectorBrief();
  const hourBrief = await getHourBrief();
  const meanFlow = await getMeanFlow(flowBrief, hourBrief, sectorBrief);

  console.log({
    startTime: startTime,
    endTime: endTime,
    flow: flowBrief,
    sector: sectorBrief,
    hour: hourBrief,
    mean: meanFlow,
  });
};

const getFlowBrief = async () => {
  const sql = `SELECT moment, content, sector, mode FROM 2kt_flow_record WHERE moment >= '${startTime}' AND moment <= '${endTime}'`;
  const data = await execSql(sql);
  if (!data || !Array.isArray(data) || !data.length) {
    return [];
  }

  const param = await getParam("NTFM_TMA_PAYLOAD_LAST");
  const tags = param.key.split(":");

  let valZuuu = 0;
  let valZutf = 0;
  let valMisc = 0;
  let valAll = 0;
  let lineCount = 0;

  const result = {};
  tags.forEach((tag, i) => {
    data.forEach((line) => {
      const day = dayjs(line.moment);
      const keyOfMon = "M_" + day.format("MM");
      if (!result[keyOfMon])
        result[keyOfMon] = {
          month: day.format("YYYY-MM"),
          valZuuu: 0,
          valZutf: 0,
          valMisc: 0,
          valAll: 0,
        };
      const target = result[keyOfMon];
      lineCount++;
      const val = parseInt(line.content[i]);
      if (tag.includes("ZUUU")) {
        target.valZuuu += val;
      } else if (tag.includes("ZUTF")) {
        target.valZutf += val;
      } else {
        target.valMisc += val;
      }
      target.valAll += val;
    });
  });

  // return { ZUUU: valZuuu, ZUTF: valZutf, Misc: valMisc, Total: valAll };
  return result;
};

const getSectorBrief = async () => {
  const sql = `SELECT startTime, endTime, count FROM 2kt_sector_record WHERE endTime >= '${startTime}' AND startTime <= '${endTime}'`;
  const data = await execSql(sql);
  if (!data || !Array.isArray(data) || !data.length) {
    return [];
  }
  const result = {};
  const rangeFrom = new Date(startTime).getTime();
  const rangeTo = new Date(endTime).getTime();
  data.forEach((row) => {
    let start = new Date(row.startTime).getTime();
    let end = new Date(row.endTime).getTime();
    start = start > rangeFrom ? start : rangeFrom;
    end = end < rangeTo ? end : rangeTo;
    const day = dayjs(start);
    const keyOfMon = "M_" + day.format("MM");
    if (!result[keyOfMon]) result[keyOfMon] = { sectorHours: 0 };
    result[keyOfMon].sectorHours +=
      (parseInt(row.count) * (end - start)) / 3600000;
  });
  Object.keys(result).forEach(
    (key) =>
      (result[key].sectorHours = Math.round(result[key].sectorHours * 10) / 10)
  );
  return result;
};

const getHourBrief = async () => {
  const sql = `SELECT recordId, userName, userId, startTime, endTime FROM 2kt_seat_hours WHERE endTime >= '${startTime}' AND startTime <= '${endTime}' AND seatRoleId != 3`;
  const data = await execSql(sql);
  if (!data || !Array.isArray(data) || !data.length) {
    return [];
  }
  const result = {};
  let hours = 0;
  const rangeFrom = new Date(startTime).getTime();
  const rangeTo = new Date(endTime).getTime();
  data.forEach((row) => {
    let start = new Date(row.startTime).getTime();
    let end = new Date(row.endTime).getTime();
    start = start > rangeFrom ? start : rangeFrom;
    end = end < rangeTo ? end : rangeTo;
    const day = dayjs(start);
    const keyOfMon = "M_" + day.format("MM");
    const keyOfUser = "U_" + row.userId;
    if (!result[keyOfMon])
      result[keyOfMon] = {
        totalHours: 0,
        memberCount: 0,
        hoursPerCapita: 0,
        memberCountOptimized: 0,
        hoursPerCapitaOptimized: 0,
        memberHours: {},
      };
    const target = result[keyOfMon];
    if (!target.memberHours[keyOfUser])
      target.memberHours[keyOfUser] = {
        userId: row.userId,
        userName: row.userName,
        hours: 0,
      };
    const duration = (end - start) / 3600000;
    target.totalHours += duration;
    target.memberHours[keyOfUser].hours += duration;
  });

  Object.keys(result).forEach((keyOfMon) => {
    const target = result[keyOfMon];
    Object.keys(target.memberHours).forEach((keyOfUser) => {
      const targetUser = target.memberHours[keyOfUser];
      target.memberCount++;
      if (targetUser.hours >= hourDispose) target.memberCountOptimized++;
    });
    target.hoursPerCapita =
      target.memberCount > 0
        ? Math.round((target.totalHours * 10) / target.memberCount) / 10
        : 0;
    target.hoursPerCapitaOptimized =
      target.memberCountOptimized > 0
        ? Math.round((target.totalHours * 10) / target.memberCountOptimized) /
          10
        : 0;

    target.totalHours = Math.round(target.totalHours * 10) / 10;
    // target.memberHours = null;
    delete target.memberHours;
  });

  return result;
};

const getMeanFlow = (flowResult, hourResult, sectorResult) => {
  const result = {};
  Object.keys(flowResult).forEach((keyOfMonth) => {
    result[keyOfMonth] = {
      perUserFlow: 0,
      perUserFlowOptimized: 0,
      perSectorFlow: 0,
    };
    if (hourResult[keyOfMonth]) {
      result[keyOfMonth].perUserFlow = Math.round(
        flowResult[keyOfMonth].valAll / hourResult[keyOfMonth].memberCount
      );
      result[keyOfMonth].perUserFlowOptimized = Math.round(
        flowResult[keyOfMonth].valAll /
          hourResult[keyOfMonth].memberCountOptimized
      );
    }
    if (sectorResult[keyOfMonth]) {
      result[keyOfMonth].perSectorFlow = Math.round(
        flowResult[keyOfMonth].valAll / sectorResult[keyOfMonth].sectorHours
      );
    }
  });

  return result;
};

main();
